<?php

require_once('Site.class.php');
require_once('Gallery.class.php');
require_once('Photo.class.php');

class GalleryManager {

    private $connection;

    const NUM_PHOTOS_PER_PAGE = 16;
    const GET_ALL_GALLERIES_QUERY = "SELECT * FROM galleries;";
    const GET_GALLERIES_BY_YEAR_QUERY = "SELECT * FROM galleries WHERE YEAR(date) = :year ORDER BY date DESC;";
    const GET_GALLERIES_BY_YEAR_BY_CREATOR_QUERY = "SELECT * FROM galleries WHERE YEAR(date) = :year AND creator = :uid ORDER BY date DESC;";
    const GET_NUMBER_PHOTOS_BY_GALLERY_ID_QUERY = "SELECT COUNT(*) AS number_photos FROM photos WHERE gallery_id = :gallery_id;";
    const GET_PHOTOS_BY_GALLERY_ID_PAGE_NUMBER = "SELECT * FROM photos WHERE gallery_id = :gallery_id LIMIT :start, :num_photos;";
    const GET_GALLERY_BY_ID = "SELECT * FROM galleries WHERE id = :id;";
    const GET_PHOTOS_BY_GALLERY_ID = "SELECT * FROM photos WHERE gallery_id = :gallery_id;";
    const SAVE_GALLERY_QUERY = "INSERT INTO galleries (title, description, folder, date, path, creator) VALUES (:title, :description, :folder, :date, :path, :creator);";
    const SAVE_PHOTO_QUERY = "INSERT INTO photos (url, gallery_id) VALUES (:url, :gallery_id);";
    const UPDATE_GALLERY = "UPDATE galleries SET title = :title, description = :description, folder = :folder WHERE id = :id;";
    const SELECT_PHOTO_INFOS = "SELECT date, path, url FROM galleries INNER JOIN photos ON galleries.id = photos.gallery_id WHERE photos.id = :id;";
    const DELETE_PHOTO = "DELETE FROM photos WHERE id = :id;";
    const DELETE_GALLERY = "DELETE FROM galleries WHERE id = :id;";
    const SELECT_PHOTOS_BY_GALLERY_ID_FOR_DELETION = "SELECT photos.id, date, path, url FROM galleries INNER JOIN photos ON galleries.id = photos.gallery_id WHERE photos.gallery_id = :gallery_id;";
    const SHOW_GALLERY = "UPDATE galleries SET visible = 1 WHERE id = :id;";
    const HIDE_GALLERY = "UPDATE galleries SET visible = 0 WHERE id = :id;";
    const GET_FOLDERS = "SELECT name, description FROM folders ORDER BY description ASC;";
    const SAVE_FOLDER = "INSERT INTO folders (name, description, superfolder) VALUES (:name, :description, :superfolder);";
    const CHECK_FOLDER = "SELECT COUNT(*) AS rowCount FROM folders WHERE name = :name;";
    const GET_LAST_GALLERIES = "SELECT * FROM galleries ORDER BY creation_date DESC LIMIT 10;";
    const GET_SUBFOLDERS = "SELECT name, description FROM folders WHERE superfolder = :superfolder ORDER BY description ASC;";
    const CHECK_ROOT_FOLDER_EXISTANCE = "SELECT COUNT(*) AS count FROM folders WHERE name = :name AND superfolder IS NULL;";
    const CHECK_FOLDER_SUPERFOLDER_EXISTANCE = "SELECT COUNT(*) AS count FROM folders WHERE name = :name AND superfolder = :superfolder;";
    const CHECK_GALLERY_FOLDER = "SELECT COUNT(*) AS count FROM galleries WHERE id = :id AND folder = :folder;";
    const GET_GALLERIES_IN_FOLDER = "SELECT * FROM galleries WHERE folder = :folder AND visible = 1;;";
    const GET_FOLDER_DESCRIPTION = "SELECT description FROM folders WHERE name = :name;";
    const GET_ROOT_FOLDERS = "SELECT name, description FROM folders WHERE superfolder IS NULL;";
    
    public function __construct() {
        $this->connection = new PDO(
                        "mysql:host=" . Site::DB_HOST . ";dbname=" . Site::DB_NAME,
                        Site::DB_USERNAME,
                        Site::DB_PASSWORD,
                        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
        );
    }

    public function save_gallery(&$g) {
        $statement = $this->connection->prepare(self::SAVE_GALLERY_QUERY);
        $statement->execute(array(
            'title' => $g->title,
            'description' => $g->description,
            'folder' => $g->folder,
            'date' => $g->date->format('Y-m-d'),
            'path' => $g->path,
            'creator' => $g->creator
        ));
        if ($statement->rowCount() <= 0)
            throw new Exception();
        $g->id = $this->connection->lastInsertId();
    }
    
    public function update_gallery($g) {
        $stmt = $this->connection->prepare(self::UPDATE_GALLERY);
        $stmt->execute(array(
                            'id' => $g->id,
                            'title' => $g->title,
                            'description' => $g->description,
                            'folder' => $g->folder
                       ));
        
        return $stmt->rowCount();
    }
    
    public function save_photo_for_gallery(&$p) {
        $statement = $this->connection->prepare(self::SAVE_PHOTO_QUERY);
        $statement->execute(array(
            'url' => $p->url,
            'gallery_id' => $p->gallery_id
        ));
        $p->id = $this->connection->lastInsertId();
        return $statement->rowCount() > 0;
    }

    public function get_galleries() {
        $statement = $this->connection->prepare(self::GET_ALL_GALLERIES_QUERY);
        $statement->execute();

        $result = array();
        $gall = null;

        while ($g = $statement->fetch(PDO::FETCH_ASSOC)) {
            $gall = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gall->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gall->{$key} = ($value == 1) ? true : false;
                } else {
                    $gall->{$key} = $value;
                }
                
            }
            array_push($result, $gall);
        }
        return $result;
    }
    
    public function get_gallery_by_id($id) {
        $statement = $this->connection->prepare(self::GET_GALLERY_BY_ID);
        $statement->execute(array('id' => $id));
        $gallery = null;
        $g = $statement->fetch(PDO::FETCH_ASSOC);
        if($g) {
            $gallery = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gallery->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gallery->{$key} = ($value == 1) ? true : false;
                } else {
                    $gallery->{$key} = $value;
                }
            }
                
        } 
        return $gallery;
    }
    
    public function get_folder_description($folder) {
        $stmt = $this->connection->prepare(self::GET_FOLDER_DESCRIPTION);
        $stmt->execute(array('name' => $folder));
        if ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
            return $r['description'];
        }
        return '';
    }
    
    public function get_galleries_by_year($y) {
        $statement = $this->connection->prepare(self::GET_GALLERIES_BY_YEAR_QUERY);
        $statement->execute(array('year' => $y));

        $result = array();
        $gall = null;

        while ($g = $statement->fetch(PDO::FETCH_ASSOC)) {
            $gall = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gall->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gall->{$key} = ($value == 1) ? true : false;
                } else {
                    $gall->{$key} = $value;
                }
            }
            array_push($result, $gall);
        }
        return $result;
    }
    
    public function get_galleries_by_year_by_creator($y, $uid) {
        $statement = $this->connection->prepare(self::GET_GALLERIES_BY_YEAR_BY_CREATOR_QUERY);
        $statement->execute(array('year' => $y, 'uid' => $uid));

        $result = array();
        $gall = null;

        while ($g = $statement->fetch(PDO::FETCH_ASSOC)) {
            $gall = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gall->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gall->{$key} = ($value == 1) ? true : false;
                } else {
                    $gall->{$key} = $value;
                }
            }
            array_push($result, $gall);
        }
        return $result;
    }

    public function get_number_pages_by_gallery_id($id) {
        $statement = $this->connection->prepare(self::GET_NUMBER_PHOTOS_BY_GALLERY_ID_QUERY);
        $statement->execute(array('gallery_id' => $id));
        if ($statement->rowCount() <= 0) {
            throw new Exception();
        }

        $r = $statement->fetch(PDO::FETCH_ASSOC);
        return (int) ceil($r['number_photos'] / self::NUM_PHOTOS_PER_PAGE);
    }
    
    public function get_galleries_in_folder($folder) {
        $statement = $this->connection->prepare(self::GET_GALLERIES_IN_FOLDER);
        $statement->execute(array('folder' => $folder));

        $result = array();
        $gall = null;

        while ($g = $statement->fetch(PDO::FETCH_ASSOC)) {
            $gall = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gall->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gall->{$key} = ($value == 1) ? true : false;
                } else {
                    $gall->{$key} = $value;
                }
            }
            array_push($result, $gall);
        }
        return $result;
    }
    
    public function get_last_galleries() {
        $statement = $this->connection->prepare(self::GET_LAST_GALLERIES);
        $statement->execute();

        $result = array();
        $gall = null;

        while ($g = $statement->fetch(PDO::FETCH_ASSOC)) {
            $gall = new Gallery();
            foreach ($g as $key => $value) {
                if ($key == 'date') {
                    $gall->{$key} = new DateTime($value);
                } else if ($key == 'visible') {
                    $gall->{$key} = ($value == 1) ? true : false;
                } else {
                    $gall->{$key} = $value;
                }
            }
            array_push($result, $gall);
        }
        return $result;
    }
    
    public function get_subfolders($folder) {
        $stmt = $this->connection->prepare(self::GET_SUBFOLDERS);
        $stmt->execute(array('superfolder' => $folder));
        $result = array();
        
        while ($f = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $result[$f['name']] = $f['description'];
        }
        
        return $result;
    }
    
    public function check_folder_gallery($g_id, $folder){
        $stmt = $this->connection->prepare(self::CHECK_GALLERY_FOLDER);
        $stmt->execute(array('id' => $g_id, 'folder' => $folder));
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        return ($r['count'] > 0);
    }
    
    public function check_folders_hierarchy($folders) {
        $stmt_root = $this->connection->prepare(self::CHECK_ROOT_FOLDER_EXISTANCE);
        $stmt_root->execute(array('name' => $folders[0]));
        $r_root = $stmt_root->fetch(PDO::FETCH_ASSOC);
        $exists = true && ($r_root['count'] > 0);
        $stmt_f_sf = $this->connection->prepare(self::CHECK_FOLDER_SUPERFOLDER_EXISTANCE);
        for($i = 1; ($i < count($folders)) && $exists; $i++) {
            $stmt_f_sf->execute(array('name' => $folders[$i], 'superfolder' => $folders[$i-1]));
            $r = $stmt_f_sf->fetch(PDO::FETCH_ASSOC);
            $exists = $exists && ($r['count'] > 0);
        }
        
        return $exists;
    }
    
    public function get_photos_by_gallery_id_page_number($id, $page) {
        if (($page <= 0) || ($page > $this->get_number_pages_by_gallery_id($id)))
            throw new Exception();

        $statement = $this->connection->prepare(self::GET_PHOTOS_BY_GALLERY_ID_PAGE_NUMBER);
        $statement->bindValue(':gallery_id', $id, PDO::PARAM_INT);
        $statement->bindValue(':start', ($page - 1) * self::NUM_PHOTOS_PER_PAGE, PDO::PARAM_INT);
        $statement->bindValue(':num_photos', self::NUM_PHOTOS_PER_PAGE, PDO::PARAM_INT);
        $statement->execute();
        if ($statement->rowCount() <= 0) {
            throw new Exception();
        }
        $photo = null;
        $result = array();
        while ($p = $statement->fetch(PDO::FETCH_ASSOC)) {
            $photo = new Photo();
            foreach ($p as $key => $value) {
                $photo->{$key} = $value;
            }
            array_push($result, $photo);
        }
        return $result;
    }
    
    public function show_gallery($id) {
        $stmt = $this->connection->prepare(self::SHOW_GALLERY);
        $stmt->execute(array('id' => $id));
        return $stmt->rowCount();
    }
    
    public function hide_gallery($id) {
        $stmt = $this->connection->prepare(self::HIDE_GALLERY);
        $stmt->execute(array('id' => $id));
        return $stmt->rowCount();
    }
    
    public function get_photos(&$g) {
        $stmt = $this->connection->prepare(self::GET_PHOTOS_BY_GALLERY_ID);
        $stmt->execute(array('gallery_id' => $g->id));
        
        $ps = array();
        $photo = null;
        
        while ($p = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $photo = new Photo();
            foreach ($p as $key => $value) {
                if ($key == 'date') {
                    $photo->{$key} = new DateTime($value);
                } else {
                    $photo->{$key} = $value;
                }
            }
            array_push($ps, $photo);
        }
        
        $g->photos = $ps;
    }
    
    public function delete_photo($id){
        $stmt_info = $this->connection->prepare(self::SELECT_PHOTO_INFOS);
        $stmt_info->execute(array('id' => $id));
        
        if ($p = $stmt_info->fetch(PDO::FETCH_ASSOC)) {
            $date = new DateTime($p['date']);
            $path = $_SERVER['DOCUMENT_ROOT'] . '/galleries/' . $date->format('Y') . '/' . $date->format('m') . '/' . $p['path'] . '/';
            $thumb_path = $path . 'thumbnails/' . $p['url'];
            $photo_path = $path . 'photos/' . $p['url'];
            if(file_exists($photo_path)) {
                unlink($photo_path);
            }
            
            if(file_exists($thumb_path)) {
                unlink($thumb_path);
            }
        }
        
        $stmt_del = $this->connection->prepare(self::DELETE_PHOTO);
        $stmt_del->execute(array('id' => $id));
        
        return $stmt_del->rowCount();
        
    }
    
    public function delete_gallery($id) {
        
        
        $stmt = $this->connection->prepare(self::SELECT_PHOTOS_BY_GALLERY_ID_FOR_DELETION);
        
        $stmt->execute(array('gallery_id' => $id));
        
        $deletion_count = 0;
        $path = null;
        $stmt_del_p = $this->connection->prepare(self::DELETE_PHOTO);
        
        while ($p = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if (!isset($date)) {
                $date = new DateTime($p['date']);
                $path = $_SERVER['DOCUMENT_ROOT'] . '/salesianichioggia.it/galleries/' . $date->format('Y') . '/' . $date->format('m') . '/' . $p['path'];
            }
            $thumb_path = $path . '/thumbnails';
            $photo_path = $path . '/photos';
            
            if (file_exists($thumb_path . '/' . $p['url'])) {
                unlink($thumb_path . '/' . $p['url']);
            }
            
            if (file_exists($photo_path . '/' . $p['url'])) {
                unlink($photo_path . '/' . $p['url']);
            }
            
            $stmt_del_p->execute(array('id' => $p['id']));
            if ($stmt_del_p->rowCount()) {
                $deletion_count++;
            }
            
        }
        
        if (file_exists($path . '/thumbnails')) {
            rmdir($path . '/thumbnails');
        }
        if (file_exists($path . '/photos')) {
            rmdir($path . '/photos');
        }
        if (file_exists($path)) {
            rmdir($path);
        }
        $stmt_del_g = $this->connection->prepare(self::DELETE_GALLERY);
        $stmt_del_g->execute(array('id' => $id));
        return $stmt_del_g->rowCount();
    }
    
    public function get_folders() {
        $stmt = $this->connection->prepare(self::GET_FOLDERS);
        $stmt->execute();
        
        $result = array();
        while($f = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $result[$f['name']] = $f['description'];
        }
        
        return $result;
    }
    
    public function get_root_folders() {
        $stmt = $this->connection->prepare(self::GET_ROOT_FOLDERS);
        $stmt->execute();
        
        $result = array();
        while($f = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $result[$f['name']] = $f['description'];
        }
        
        return $result;
    }
    
    public function save_folder($name, $description, $superfolder) {
        $stmt = $this->connection->prepare(self::SAVE_FOLDER);
        $stmt->bindValue(':name', $name, PDO::PARAM_STR);
        $stmt->bindValue(':description', $description, PDO::PARAM_STR);
        if ($superfolder == null) {
            $stmt->bindValue(':superfolder', null, PDO::PARAM_NULL);
        } else {
            $stmt->bindValue(':superfolder', $superfolder, PDO::PARAM_STR);
        }
        $stmt->execute();
        return $stmt->rowCount();
    }
    
    public function check_folder($name) {
        $stmt = $this->connection->prepare(self::CHECK_FOLDER);
        $stmt->execute(array('name' => $name));
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        return $r['rowCount'];
    }

}

?>